package com.edu.publisher.mapper;

import com.edu.publisher.bean.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * @author zcx
 * @create 2022-10-19 19:48
 * 交易域统计mapper接口
 */
public interface TradeStatsMapper {
    //获取某一天总加购人数
    @Select("select sum(cart_add_uu_ct) cart_add_uu_ct from dws_trade_cart_add_uu_window where toYYYYMMDD(stt)=#{date}")
    BigDecimal selectCAU(Integer date);

    //获取某一天交易总金额
    @Select("select sum(final_amount) final_amount from dws_trade_course_order_window where toYYYYMMDD(stt)=#{date}")
    Double selectFinalAmount(Integer date);

    //获取某一天下单情况
    @Select("select '下单数' type, sum(order_count) value from dws_trade_course_order_window " +
            "where toYYYYMMDD(stt) = #{date} " +
            "union all " +
            "select '下单人数' type, sum(order_uu_count) value from dws_trade_course_order_window where toYYYYMMDD(stt) = #{date} ")
    List<TradeStatsBean> selectTradeStats(Integer date);

    //获取某一天不同省份交易额
    @Select("select province_name,sum(final_amount) final_amount from dws_trade_province_order_window " +
            "where toYYYYMMDD(stt)=#{date} group by province_name")
    List<TradeProvinceOrderAmount> selectProvinceAmount(Integer date);

    //获取某一天各来源的交易额
    @Select("select source_id,source_name, sum(final_amount) final_amount from dws_trade_source_order_window where " +
            "toYYYYMMDD" +
            "(stt) = " +
            "#{date} group by" +
            " toYYYYMMDD(stt),source_id ,source_name order by final_amount desc")
    List<TradeScFinalAmount> selectScFinalAmount(Integer date);

    //获取某一天各来源的下单独立用户数
    @Select("select source_id,source_name, sum(order_uu_count) order_uu_count from dws_trade_source_order_window where toYYYYMMDD(stt) = " +
            "#{date} group by toYYYYMMDD(stt),source_id,source_name order by order_uu_count desc")
    List<TradeScOdUvCt> selectScOdUvCt(Integer date);

    //获取某一天各来源的下单次数
    @Select("select source_id,source_name, sum(order_count) order_count from dws_trade_source_order_window where toYYYYMMDD(stt) " +
            "= " +
            "#{date} group by toYYYYMMDD(stt),source_id,source_name order by order_count desc")
    List<TradeScOdCt> selectScOdCt(Integer date);

    //获取某一天各来源的下单转化率
    @Select("select source_id,source_name, sum(order_count) / sum(order_uu_count) sc_od_percent_conversion from " +
            "dws_trade_source_order_window where " +
            "toYYYYMMDD(stt) = #{date} " +
            "group by toYYYYMMDD(stt),source_id,source_name order by sc_od_percent_conversion desc")
    List<TradeScOdPercentConversion> selectScOdPeCo(Integer date);
}
